package com.coocaa.aries.codegen.util;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import com.baomidou.mybatisplus.generator.config.rules.DbColumnType;
import com.coocaa.aries.codegen.constant.SqlConstants;
import com.coocaa.aries.codegen.convert.ColumnListConvert;
import com.coocaa.aries.codegen.convert.MySqlTypeConvert;
import com.coocaa.aries.codegen.enums.QuerySQL;
import com.coocaa.aries.codegen.model.ColumnConfigModel;
import com.coocaa.aries.codegen.model.TableInfoModel;
import com.coocaa.aries.codegen.model.query.DbTableQueryModel;
import com.coocaa.aries.core.base.model.PageResultModel;
import com.coocaa.aries.core.tool.string.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.Assert;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author : bijiahao
 * @date : 2019/4/19.
 * @description:
 */
public class SqlUtil {
    private Connection connection;
    private DbTableQueryModel dbTableQueryModel;

    public SqlUtil(DbTableQueryModel model) {
        Assert.hasText(model.getUrl(), "数据库url不能为空！");
        Assert.hasText(model.getUsername(), "数据库用户名不能为空！");
        Assert.hasText(model.getPassword(), "数据库密码不能为空！");
        DataSourceConfig dataSourceConfig = convert2DataSourceConfig(model);
        this.connection = dataSourceConfig.getConn();
        this.dbTableQueryModel = model;
    }

    private static DbColumnType getDbColumnType(String jdbcType) {
        return MySqlTypeConvert.processTypeConvert(jdbcType);
    }

    private static boolean includeSuperEntityColumns(String columnName) {
        for (String column : SqlConstants.SUPER_ENTITY_COLUMNS) {
            if (column.equals(columnName)) {
                return true;
            }
        }
        return false;
    }

    private static DataSourceConfig convert2DataSourceConfig(DbTableQueryModel model) {
        DataSourceConfig dataSourceConfig = new DataSourceConfig();
        dataSourceConfig.setDbType(DbType.MYSQL);
        dataSourceConfig.setDriverName(SqlConstants.DRIVER);
        dataSourceConfig.setUrl(model.getUrl());
        dataSourceConfig.setUsername(model.getUsername());
        dataSourceConfig.setPassword(model.getPassword());
        return dataSourceConfig;
    }

    private static String getShowTableInfoSql(String query, QuerySQL querySQL) {

        StringBuilder sql = new StringBuilder(querySQL.getTableCommentsSql());
        if (StringUtils.isNotBlank(query)) {
            query = query.trim() + "%";
            sql.append(" WHERE ")
                    .append(querySQL.getTableName())
                    .append(" LIKE ")
                    .append(query)
                    .append(" OR ")
                    .append(querySQL.getTableComment())
                    .append(" LIKE ")
                    .append(query);
        }
        return sql.toString();
    }

    private static String getShowColumnInfoSql(String tableName, QuerySQL querySQL) {
        String sql = String.format(querySQL.getTableFieldsSql(), tableName);
        return sql;

    }

    public PageResultModel<TableInfoModel> listTableInfo() {
        PreparedStatement preparedStatement = null;
        QuerySQL querySQL = QuerySQL.MYSQL;
        List<TableInfoModel> tableList = new ArrayList<>();
        int total = 0;
        TableInfoModel tableConfigModel;
        try {
            String tableCountSql = getTableCountSql();
            preparedStatement = connection.prepareStatement(tableCountSql);
            ResultSet count = preparedStatement.executeQuery();
            if (count.next()) {
                total = count.getInt(1);
            }
            if (total > 0) {
                String tableCommentsSql = getTableInfoQuerySql();
                preparedStatement = connection.prepareStatement(tableCommentsSql);
                ResultSet results = preparedStatement.executeQuery();
                while (results.next()) {
                    String tableName = results.getString(querySQL.getTableName());
                    if (StringUtils.isNotEmpty(tableName)) {
                        String tableComment = results.getString(querySQL.getTableComment());
                        tableConfigModel = new TableInfoModel();
                        tableConfigModel.setTableName(tableName);
                        tableConfigModel.setComment(tableComment);

                        tableList.add(convertTableColumns(tableName, querySQL, tableConfigModel));
                    } else {
                        System.err.println("当前数据库为空！！！");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return PageResultModel.getPageResultModel(total, tableList);
    }

    private String getTableCountSql() {
        return String.format(SqlConstants.TABLE_COUNT_QUERY_SQL, getDbName());
    }

    private String getTableInfoQuerySql() {
        StringBuilder sql = new StringBuilder();

        int limit = dbTableQueryModel.getRows();
        int offset = (dbTableQueryModel.getPage() - 1) * dbTableQueryModel.getRows();
        sql.append(String.format(SqlConstants.TABLE_INFO_QUERY_SQL, getDbName()));
        if (StringUtils.isNotBlank(dbTableQueryModel.getKeyword())) {
            String query = "%" + dbTableQueryModel.getKeyword() + "%";
            sql.append(" and TABLE_NAME LIKE ")
                    .append(query)
                    .append(" OR TABLE_COMMENT LIKE ")
                    .append(query);
        }
        sql.append(" ORDER BY CREATE_TIME DESC")
                .append(" limit ")
                .append(limit)
                .append(" offset ")
                .append(offset);
        return sql.toString();
    }

    private String getDbName() {
        String url = dbTableQueryModel.getUrl();
        int start = url.lastIndexOf("/");
        int end = url.lastIndexOf("?");
        return url.substring(start + 1, end);
    }

    private TableInfoModel convertTableColumns(String tabName, QuerySQL querySQL, TableInfoModel tableConfigModel) {
        boolean haveId = false;
        List<ColumnConfigModel> columnList = new ArrayList<>();
        //不存在的字段
        List<ColumnConfigModel> notExistColumnList = new ArrayList<>();
        //和父类相同的字段
        List<ColumnConfigModel> commonColumnList = new ArrayList<>();
        String tableFieldsSql = getShowColumnInfoSql(tabName, querySQL);

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(tableFieldsSql);
            ResultSet results = preparedStatement.executeQuery(tableFieldsSql);
            while (results.next()) {
                ColumnConfigModel columnConfigModel = new ColumnConfigModel();
                String key = results.getString(querySQL.getFieldKey());
                // 避免多重主键设置，目前只取第一个找到ID，并放到list中的索引为0的位置
                boolean isId = StringUtils.isNotEmpty(key) && key.toUpperCase().equals("PRI");
                // 处理ID
                if (isId && !haveId) {
                    columnConfigModel.setKey(key);
                    haveId = true;
                }
                // 处理其它信息
                columnConfigModel.setColumnName(results.getString(querySQL.getFieldName()));
                columnConfigModel.setColumnType(getDbColumnType(results.getString(querySQL.getFieldType())));
                columnConfigModel.setJavaType(columnConfigModel.getColumnType().getType());
                columnConfigModel.setFieldName(StringUtil.underlineToCamel(columnConfigModel.getColumnName()));
                columnConfigModel.setComment(results.getString(querySQL.getFieldComment()));
                if (includeSuperEntityColumns(columnConfigModel.getColumnName())) {
                    //公共字段
                    commonColumnList.add(columnConfigModel);
                } else {
                    columnList.add(columnConfigModel);
                }
            }
            notExistColumnList = ColumnListConvert.convertNotExistColumnList(commonColumnList);
        } catch (SQLException e) {
            System.err.println("SQL Exception：" + e.getMessage());
        }
        tableConfigModel.setColumnList(columnList);
        tableConfigModel.setNotExistColumnList(notExistColumnList);
        return tableConfigModel;
    }

}
